﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_sicomill
{
    public DAL_tbl_sicomill()
    {

    }



    public List<tbl_sicomill> select(int ID)
    {
        List<tbl_sicomill> list = new List<tbl_sicomill>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,PO_Number,Work_Order_Number,Total_Input_RM_Si,Mill_Selection,Rotor_RPM,Gas_Pressure,Feed_rate,Total_Output_Product,Date,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_sicomill where ID=@ID";
        cmd.Parameters.AddWithValue("@ID", ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_sicomill()
            {
                ID = Convert.ToInt32(dr["ID"]),
                PO_Number = dr["PO_Number"].ToString(),
                Work_Order_Number = dr["Work_Order_Number"].ToString(),
                Total_Input_RM_Si = Convert.ToDecimal(dr["Total_Input_RM_Si"]),
                Mill_Selection = Convert.ToInt32(dr["Mill_Selection"]),
                Rotor_RPM = Convert.ToDecimal(dr["Rotor_RPM"]),
                Gas_Pressure = Convert.ToDecimal(dr["Gas_Pressure"]),
                Feed_rate = Convert.ToDecimal(dr["Feed_rate"]),
                Total_Output_Product = Convert.ToDecimal(dr["Total_Output_Product"]),
                Date = dr["Date"].toDateTimeWithoutError(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError(),
                sicomill_inputvessels = getsicomill_inputvessels(Convert.ToInt32(dr["ID"])),
                sicomill_outputvessels = getsicomill_outputvessels(Convert.ToInt32(dr["ID"]))
            });
        }

        return list;
    }


    private List<tbl_sicomill_inputvessels1> getsicomill_inputvessels(int SicoMil_ID)
    {
        List<tbl_sicomill_inputvessels1> list = new List<tbl_sicomill_inputvessels1>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID, SicoMil_ID, DRUM_BAG, DRUM_BAG_Number from tbl_sicomill_inputvessels where SicoMil_ID=@SicoMil_ID";
        cmd.Parameters.AddWithValue("@SicoMil_ID", SicoMil_ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_sicomill_inputvessels1() {
                SicoMil_ID = Convert.ToInt32(dr["SicoMil_ID"]),
                DRUM_BAG = Convert.ToInt32(dr["DRUM_BAG"]),
                DRUM_BAG_Number = dr["DRUM_BAG_Number"].ToString()
            });
        }

        return list;
    }


    private List<tbl_sicomill_outputvessels1> getsicomill_outputvessels(int SicoMil_ID)
    {
        List<tbl_sicomill_outputvessels1> list = new List<tbl_sicomill_outputvessels1>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID, SicoMil_ID, DRUM_BAG, DRUM_BAG_Number from tbl_sicomill_outputvessels where SicoMil_ID=@SicoMil_ID";
        cmd.Parameters.AddWithValue("@SicoMil_ID", SicoMil_ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_sicomill_outputvessels1()
            {
                SicoMil_ID = Convert.ToInt32(dr["SicoMil_ID"]),
                DRUM_BAG = Convert.ToInt32(dr["DRUM_BAG"]),
                DRUM_BAG_Number = dr["DRUM_BAG_Number"].ToString()
            });
        }

        return list;
    }


    public List<tbl_sicomill> selectAll()
    {
        List<tbl_sicomill> list = new List<tbl_sicomill>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,PO_Number,Work_Order_Number,Total_Input_RM_Si,Mill_Selection,Rotor_RPM,Gas_Pressure,Feed_rate,Total_Output_Product,Date,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_sicomill Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_sicomill()
            {
                ID = Convert.ToInt32(dr["ID"]),
                PO_Number = dr["PO_Number"].ToString(),
                Work_Order_Number = dr["Work_Order_Number"].ToString(),
                Total_Input_RM_Si = Convert.ToDecimal(dr["Total_Input_RM_Si"]),
                Mill_Selection = Convert.ToInt32(dr["Mill_Selection"]),
                Rotor_RPM = Convert.ToDecimal(dr["Rotor_RPM"]),
                Gas_Pressure = Convert.ToDecimal(dr["Gas_Pressure"]),
                Feed_rate = Convert.ToDecimal(dr["Feed_rate"]),
                Total_Output_Product = Convert.ToDecimal(dr["Total_Output_Product"]),
                Date = dr["Date"].toDateTimeWithoutError(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError(),
                sicomill_inputvessels = getsicomill_inputvessels(Convert.ToInt32(dr["ID"])),
                sicomill_outputvessels = getsicomill_outputvessels(Convert.ToInt32(dr["ID"]))
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"SELECT ID,PO_Number,Work_Order_Number,Total_Input_RM_Si,mm.MILL_TYPE as 'Mill_Selection',Rotor_RPM,Gas_Pressure,Feed_rate
,Total_Output_Product,Date as 'Date',CASE WHEN sm.IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',sm.CREATED_BY,sm.CREATED_DATE as CREATED_DATE,sm.UPDATED_BY,sm.UPDATED_DATE 
FROM tbl_sicomill sm left outer join tbl_mills_master mm on sm.Mill_Selection=mm.MILL_ID
                            Order By ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }


    public int insert(String PO_Number, String Work_Order_Number, Decimal Total_Input_RM_Si, int Mill_Selection, Decimal Rotor_RPM, Decimal Gas_Pressure, Decimal Feed_rate, Decimal Total_Output_Product, DateTime Date, String CREATED_BY, DateTime CREATED_DATE, List<tbl_sicomill_inputvessels1> list_sicomill_inputvessels, List<tbl_sicomill_outputvessels1> list_sicomill_outputvessels)
    {
        int i = 0;
        string sql = @"Insert into tbl_sicomill(PO_Number, Work_Order_Number, Total_Input_RM_Si, Mill_Selection, Rotor_RPM, Gas_Pressure, Feed_rate, Total_Output_Product, Date, CREATED_BY, CREATED_DATE)
                        values(@PO_Number, @Work_Order_Number, @Total_Input_RM_Si, @Mill_Selection, @Rotor_RPM, @Gas_Pressure, @Feed_rate, @Total_Output_Product, @Date, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@PO_Number", PO_Number);
        cmd.Parameters.AddWithValue("@Work_Order_Number", Work_Order_Number);
        cmd.Parameters.AddWithValue("@Total_Input_RM_Si", Total_Input_RM_Si);
        cmd.Parameters.AddWithValue("@Mill_Selection", Mill_Selection);
        cmd.Parameters.AddWithValue("@Rotor_RPM", Rotor_RPM);
        cmd.Parameters.AddWithValue("@Gas_Pressure", Gas_Pressure);
        cmd.Parameters.AddWithValue("@Feed_rate", Feed_rate);
        cmd.Parameters.AddWithValue("@Total_Output_Product", Total_Output_Product);
        cmd.Parameters.AddWithValue("@Date", Date);
        cmd.Parameters.AddWithValue("@CREATED_BY", HttpContext.Current.User.Identity.Name);
        cmd.Parameters.AddWithValue("@CREATED_DATE",DateTime.Now);

        i = DBUtil.ExecuteNonQuery(cmd);

        string sqltxt = "select ifnull(Max(ID),0) from tbl_sicomill";
        cmd.CommandText = sqltxt;
        cmd.Parameters.Clear();
        int sicomill_ID = Convert.ToInt32(DBUtil.ExecuteScalar(cmd));

        insert_sicomill_inputvessels(list_sicomill_inputvessels, sicomill_ID);
        insert_sicomill_outputvessels(list_sicomill_outputvessels, sicomill_ID);

        return i;
    }


    private void insert_sicomill_inputvessels(List<tbl_sicomill_inputvessels1> list, int sicoMill_ID)
    {
        string sql = @"Insert into tbl_sicomill_inputvessels(SicoMil_ID, DRUM_BAG, DRUM_BAG_Number)
                        values(@SicoMil_ID, @DRUM_BAG, @DRUM_BAG_Number)";

        MySqlCommand cmd = new MySqlCommand(sql);

        foreach (tbl_sicomill_inputvessels1 item in list)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
            cmd.Parameters.AddWithValue("@DRUM_BAG", item.DRUM_BAG);
            cmd.Parameters.AddWithValue("@DRUM_BAG_Number", item.DRUM_BAG_Number);
            DBUtil.ExecuteNonQuery(cmd);
        }
    }

    private void insert_sicomill_outputvessels(List<tbl_sicomill_outputvessels1> list, int sicoMill_ID)
    {
        string sql = @"Insert into tbl_sicomill_outputvessels(SicoMil_ID, DRUM_BAG, DRUM_BAG_Number)
                        values(@SicoMil_ID, @DRUM_BAG, @DRUM_BAG_Number)";

        MySqlCommand cmd = new MySqlCommand(sql);

        foreach (tbl_sicomill_outputvessels1 item in list)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
            cmd.Parameters.AddWithValue("@DRUM_BAG", item.DRUM_BAG);
            cmd.Parameters.AddWithValue("@DRUM_BAG_Number", item.DRUM_BAG_Number);
            DBUtil.ExecuteNonQuery(cmd);
        }
    }


    public int update(int ID, String PO_Number, String Work_Order_Number, Decimal Total_Input_RM_Si, int Mill_Selection, Decimal Rotor_RPM, Decimal Gas_Pressure, Decimal Feed_rate, Decimal Total_Output_Product, DateTime Date, String UPDATED_BY, DateTime UPDATED_DATE, List<tbl_sicomill_inputvessels1> list_sicomill_inputvessels, List<tbl_sicomill_outputvessels1> list_sicomill_outputvessels)
    {
        int i = 0;
        string sql = @"Update tbl_sicomill SET PO_Number=@PO_Number, Work_Order_Number=@Work_Order_Number, Total_Input_RM_Si=@Total_Input_RM_Si, Mill_Selection=@Mill_Selection, Rotor_RPM=@Rotor_RPM, Gas_Pressure=@Gas_Pressure, Feed_rate=@Feed_rate, Total_Output_Product=@Total_Output_Product, Date=@Date, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@PO_Number", PO_Number);
        cmd.Parameters.AddWithValue("@Work_Order_Number", Work_Order_Number);
        cmd.Parameters.AddWithValue("@Total_Input_RM_Si", Total_Input_RM_Si);
        cmd.Parameters.AddWithValue("@Mill_Selection", Mill_Selection);
        cmd.Parameters.AddWithValue("@Rotor_RPM", Rotor_RPM);
        cmd.Parameters.AddWithValue("@Gas_Pressure", Gas_Pressure);
        cmd.Parameters.AddWithValue("@Feed_rate", Feed_rate);
        cmd.Parameters.AddWithValue("@Total_Output_Product", Total_Output_Product);
        cmd.Parameters.AddWithValue("@Date", Date);
        cmd.Parameters.AddWithValue("@UPDATED_BY", HttpContext.Current.User.Identity.Name);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", DateTime.Now);

        i = DBUtil.ExecuteNonQuery(cmd);

        int sicomill_ID = ID;

        update_sicomill_inputvessels(list_sicomill_inputvessels, sicomill_ID);
        update_sicomill_outputvessels(list_sicomill_outputvessels, sicomill_ID);

        return i;
    }

    private void update_sicomill_inputvessels(List<tbl_sicomill_inputvessels1> list, int sicoMill_ID)
    {
        BLL_tbl_sicomill bll = new BLL_tbl_sicomill();
        DataTable dt = bll.getDataSicomill_Inputvessels(sicoMill_ID);

        int i = 0;
        foreach (tbl_sicomill_inputvessels1 item in list)
        {
            string sql = "Update tbl_sicomill_inputvessels set DRUM_BAG=@DRUM_BAG, DRUM_BAG_Number=@DRUM_BAG_Number where SicoMil_ID=@SicoMil_ID and ID=" + dt.Rows[i]["ID"];

            MySqlCommand cmd = new MySqlCommand(sql);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
            cmd.Parameters.AddWithValue("@DRUM_BAG", item.DRUM_BAG);
            cmd.Parameters.AddWithValue("@DRUM_BAG_Number", item.DRUM_BAG_Number);
            DBUtil.ExecuteNonQuery(cmd);
            i++;
        }
    }

    private void update_sicomill_outputvessels(List<tbl_sicomill_outputvessels1> list, int sicoMill_ID)
    {
        BLL_tbl_sicomill bll = new BLL_tbl_sicomill();
        DataTable dt = bll.getDataSicomill_Outputvessels(sicoMill_ID);

        int i = 0;

        foreach (tbl_sicomill_outputvessels1 item in list)
        {
            string sql = "Update tbl_sicomill_outputvessels set DRUM_BAG=@DRUM_BAG, DRUM_BAG_Number=@DRUM_BAG_Number where SicoMil_ID=@SicoMil_ID and ID=" + dt.Rows[i]["ID"];

            MySqlCommand cmd = new MySqlCommand(sql);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
            cmd.Parameters.AddWithValue("@DRUM_BAG", item.DRUM_BAG);
            cmd.Parameters.AddWithValue("@DRUM_BAG_Number", item.DRUM_BAG_Number);
            DBUtil.ExecuteNonQuery(cmd);
            i++;
        }
    }

    public int delete(int ID)
    {
        int i = 0;
        delete_sicomill_inputvessels(ID);
        delete_sicomill_outputvessels(ID);
        string sql = "Delete FROM tbl_sicomill WHERE ID=@ID";
        MySqlCommand cmd = new MySqlCommand(sql);
        cmd.Parameters.AddWithValue("@ID", ID);
        i = DBUtil.ExecuteNonQuery(cmd);        

        return i;
    }


    private void delete_sicomill_inputvessels( int sicoMill_ID)
    {
        int i = 0;
        string sql = "delete FROM tbl_sicomill_inputvessels where SicoMil_ID=@SicoMil_ID";
        MySqlCommand cmd = new MySqlCommand(sql);        
        cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
        i= DBUtil.ExecuteNonQuery(cmd);
       
    }

    private void delete_sicomill_outputvessels(int sicoMill_ID)
    {
        int i = 0;
        string sql = "delete FROM tbl_sicomill_outputvessels where SicoMil_ID=@SicoMil_ID";
        MySqlCommand cmd = new MySqlCommand(sql);
        cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
        i= DBUtil.ExecuteNonQuery(cmd);
        
    }


    public DataTable getDateByPO_Number(string PO_Number)
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "select * from tbl_sicomill where PO_Number=@PO_Number";
        cmd.Parameters.AddWithValue("@PO_Number", PO_Number);

        try
        {
            return DBUtil.ExecuteDataTable(cmd);
        }
        finally
        {

            cmd = null;
        }
    }


    public int countSicomill_Inputvessels(int sicoMill_ID)
    {
        int i = 0;
        string sql = @"SELECT count(1) FROM tbl_sicomill_inputvessels where SicoMil_ID=@SicoMil_ID";
        MySqlCommand cmd = new MySqlCommand(sql);
        cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
        i = Convert.ToInt32(DBUtil.ExecuteScalar(cmd));
        return i;

    }

    public int countSicomill_Outputvessels(int sicoMill_ID)
    {
        int i = 0;
        string sql = @"SELECT count(1) FROM tbl_sicomill_outputvessels where SicoMil_ID=@SicoMil_ID";
        MySqlCommand cmd = new MySqlCommand(sql);
        cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
        i = Convert.ToInt32(DBUtil.ExecuteScalar(cmd));
        return i;

    }

    public DataTable getDataSicomill_Inputvessels(int sicoMill_ID)
    {
        string sql = @"SELECT * FROM tbl_sicomill_inputvessels where SicoMil_ID=@SicoMil_ID";
        MySqlCommand cmd = new MySqlCommand(sql);
        cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
        try
        {
            return DBUtil.ExecuteDataTable(cmd);
        }
        finally
        {

            cmd = null;
        }
    }

    public DataTable getDataSicomill_Outputvessels(int sicoMill_ID)
    {
        string sql = @"SELECT * FROM tbl_sicomill_outputvessels where SicoMil_ID=@SicoMil_ID";
        MySqlCommand cmd = new MySqlCommand(sql);
        cmd.Parameters.AddWithValue("@SicoMil_ID", sicoMill_ID);
        try
        {
            return DBUtil.ExecuteDataTable(cmd);
        }
        finally
        {

            cmd = null;
        }
    }
}